﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace LoomDataEmulator.Data
{
    public class SQLHandler: IDisposable
    {
        public string Password { get; set; }

        public string User { get; set; }

        public string DataBaseName { get; set; }

        public string DataSource { get; set; }

        public bool IntegratedSecurity { get; set; }

        private SqlConnection _sqlConnection;

        public SqlConnection SqlConnection
        {
            get { return _sqlConnection; }
            set { _sqlConnection = value; }
        }  

        public void Connect()
        {
            var stringConnBuilder = new SqlConnectionStringBuilder();

            if (!IntegratedSecurity)
            {
                stringConnBuilder.UserID = User;
                stringConnBuilder.Password = Password;
            }
            stringConnBuilder.DataSource = DataSource;
            stringConnBuilder.IntegratedSecurity = true;
            stringConnBuilder.InitialCatalog = DataBaseName;
            stringConnBuilder.Pooling = false;

            _sqlConnection = new SqlConnection(stringConnBuilder.ConnectionString);

            try
            {
                _sqlConnection.Open();
            }
            catch (Exception)
            {
                throw;
            }

        }

        public void Close()
        {
            if (_sqlConnection != null && _sqlConnection.State == ConnectionState.Open)
            {
                _sqlConnection.Close();
            }
        }

        public void write(FileMetaData metaData)
        {

            if (metaData == null)
                throw new NullReferenceException("Cannot use a null metadata");

            StringBuilder strBuilder = new StringBuilder();

            foreach (var currentfieldValue in metaData.FieldsValue)
            {
                strBuilder.AppendLine(string.Format("INSERT INTO {0} ({1}) VALUES ({2}) ", metaData.Table.Name, metaData.TableFields, currentfieldValue));
            }

            try
            {
                using (var sqlCommand = _sqlConnection.CreateCommand())
                {
                    var fullCommandText = strBuilder.ToString();

                    sqlCommand.CommandText = fullCommandText;

                    sqlCommand.ExecuteNonQuery();
                }

            }
            catch (Exception)
            {
                throw;
            }
            

        }

        #region IDisposable Members

        public void Dispose()
        {
            if(_sqlConnection != null)
            {
                if(_sqlConnection.State != ConnectionState.Closed)
                {
                    _sqlConnection.Close();
                }

                _sqlConnection.Dispose();
                _sqlConnection = null;
            }
        }

        #endregion
    }
}
